﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using Excel;
using System.Reflection;

namespace Ketoan
{
	/// <summary>
	/// Summary description for frmCongno_Nguon.
	/// </summary>
	public class frmCongno_Nguon : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.CheckBox chkNgayhd;
		private System.Windows.Forms.Label label5;
		private System.Windows.Forms.TextBox txtMa;
		private System.Windows.Forms.TextBox txtNhacc;
		private System.Windows.Forms.CheckedListBox ckNguon;
		private System.Windows.Forms.CheckedListBox ckLoai;
		private System.Windows.Forms.Button butKetthuc;
		private System.Windows.Forms.Button butIn;
		private System.Windows.Forms.CheckBox chkNgaysp;
		private LibList.List lstNhacc;
		private LibKetoan.AccessData d;
		private string _amanguon="",_amaketoan="",_amanhacc="",_angaybaocao="",m_userroot="";
		private DataSet dsCongno;
		private DataSet dsxml;
		private System.Data.DataTable dtNhomketoan=new System.Data.DataTable();
		private System.Data.DataTable dtNguon=new System.Data.DataTable();
		private System.Data.DataTable dtNhomNguon=new System.Data.DataTable();
		private System.Data.DataTable dtNhomNguonbc=new System.Data.DataTable();
		private System.Data.DataSet dsNhacungcap=new DataSet();
		private int i_nhomkho=0;
		private string _admnguon="30@45@60@75@90@91";
		private Excel.Application oxl;
		private Excel._Workbook owb;
		private Excel._Worksheet osheet;
		private Excel.Range orange;
		private System.Windows.Forms.CheckBox chkChitiet;
		private System.Windows.Forms.Label label4;
		private System.Windows.Forms.DateTimePicker txtTN;
		private System.Windows.Forms.DateTimePicker txtDN;
		private System.Windows.Forms.ToolTip toolTip1;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.Label lblNhomNguon;
		private System.Windows.Forms.CheckBox chkNhomnguon;
		private System.Windows.Forms.CheckBox chkNgayud;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.ComboBox cbNhomkho;
		private System.Windows.Forms.Label label6;
		private System.ComponentModel.IContainer components;

		public frmCongno_Nguon(LibKetoan.AccessData d_acc, int inhomkho)
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
			d=d_acc;
			m_userroot=d.user.ToLower();
			i_nhomkho=inhomkho;
			DataSet _ds=d.get_data("select rownum as stt,a.ma,a.ten||' '||a.hamluong||'('||a.tenhc||')' as ten,a.dang,c.ten as nuoc,d.ten as hang from "+m_userroot+".d_dmbd a, "+m_userroot+".d_dmnhomkt b,"+m_userroot+".d_dmnuoc c,"+m_userroot+".d_dmhang d where a.sotk=b.id(+) and b.id is null and a.manuoc=c.id(+) and a.mahang=d.id(+) and a.nhom="+i_nhomkho.ToString()+"");
			if(_ds.Tables[0].Rows.Count>0)
			{
				frmDmbdchuakhainhomkt f = new frmDmbdchuakhainhomkt(_ds);
				f.ShowDialog();
			}
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.components = new System.ComponentModel.Container();
			System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(frmCongno_Nguon));
			this.txtTN = new System.Windows.Forms.DateTimePicker();
			this.label1 = new System.Windows.Forms.Label();
			this.chkNgayhd = new System.Windows.Forms.CheckBox();
			this.chkNgaysp = new System.Windows.Forms.CheckBox();
			this.txtMa = new System.Windows.Forms.TextBox();
			this.label5 = new System.Windows.Forms.Label();
			this.txtNhacc = new System.Windows.Forms.TextBox();
			this.ckNguon = new System.Windows.Forms.CheckedListBox();
			this.ckLoai = new System.Windows.Forms.CheckedListBox();
			this.butKetthuc = new System.Windows.Forms.Button();
			this.butIn = new System.Windows.Forms.Button();
			this.lstNhacc = new LibList.List();
			this.chkChitiet = new System.Windows.Forms.CheckBox();
			this.txtDN = new System.Windows.Forms.DateTimePicker();
			this.label4 = new System.Windows.Forms.Label();
			this.toolTip1 = new System.Windows.Forms.ToolTip(this.components);
			this.lblNhomNguon = new System.Windows.Forms.Label();
			this.chkNhomnguon = new System.Windows.Forms.CheckBox();
			this.label2 = new System.Windows.Forms.Label();
			this.chkNgayud = new System.Windows.Forms.CheckBox();
			this.label3 = new System.Windows.Forms.Label();
			this.cbNhomkho = new System.Windows.Forms.ComboBox();
			this.label6 = new System.Windows.Forms.Label();
			this.SuspendLayout();
			// 
			// txtTN
			// 
			this.txtTN.CustomFormat = "dd/MM/yyyy HH:mm";
			this.txtTN.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.txtTN.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
			this.txtTN.Location = new System.Drawing.Point(58, 8);
			this.txtTN.Name = "txtTN";
			this.txtTN.Size = new System.Drawing.Size(114, 21);
			this.txtTN.TabIndex = 0;
			this.txtTN.Value = new System.DateTime(2005, 5, 26, 11, 8, 30, 30);
			this.txtTN.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(0, 5);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(56, 23);
			this.label1.TabIndex = 11;
			this.label1.Text = "Từ ngày :";
			this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
			// 
			// chkNgayhd
			// 
			this.chkNgayhd.Checked = true;
			this.chkNgayhd.CheckState = System.Windows.Forms.CheckState.Checked;
			this.chkNgayhd.Location = new System.Drawing.Point(58, 30);
			this.chkNgayhd.Name = "chkNgayhd";
			this.chkNgayhd.Size = new System.Drawing.Size(93, 24);
			this.chkNgayhd.TabIndex = 2;
			this.chkNgayhd.Text = "Ngày hóa đơn";
			this.chkNgayhd.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			this.chkNgayhd.CheckedChanged += new System.EventHandler(this.chkNgayhd_CheckedChanged);
			// 
			// chkNgaysp
			// 
			this.chkNgaysp.Location = new System.Drawing.Point(156, 30);
			this.chkNgaysp.Name = "chkNgaysp";
			this.chkNgaysp.Size = new System.Drawing.Size(95, 24);
			this.chkNgaysp.TabIndex = 3;
			this.chkNgaysp.Text = "Ngày số phiếu";
			this.chkNgaysp.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			this.chkNgaysp.CheckedChanged += new System.EventHandler(this.chkNgaysp_CheckedChanged);
			// 
			// txtMa
			// 
			this.txtMa.BackColor = System.Drawing.SystemColors.HighlightText;
			this.txtMa.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.txtMa.Location = new System.Drawing.Point(58, 76);
			this.txtMa.Name = "txtMa";
			this.txtMa.Size = new System.Drawing.Size(48, 21);
			this.txtMa.TabIndex = 4;
			this.txtMa.Text = "";
			this.txtMa.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			this.txtMa.Validated += new System.EventHandler(this.txtMa_Validated);
			// 
			// label5
			// 
			this.label5.Location = new System.Drawing.Point(4, 76);
			this.label5.Name = "label5";
			this.label5.Size = new System.Drawing.Size(52, 23);
			this.label5.TabIndex = 12;
			this.label5.Text = "Đơn vị :";
			this.label5.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
			// 
			// txtNhacc
			// 
			this.txtNhacc.BackColor = System.Drawing.SystemColors.HighlightText;
			this.txtNhacc.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.txtNhacc.Location = new System.Drawing.Point(107, 76);
			this.txtNhacc.Name = "txtNhacc";
			this.txtNhacc.Size = new System.Drawing.Size(237, 21);
			this.txtNhacc.TabIndex = 5;
			this.txtNhacc.Text = "";
			this.txtNhacc.KeyDown += new System.Windows.Forms.KeyEventHandler(this.txtNhacc_KeyDown);
			this.txtNhacc.TextChanged += new System.EventHandler(this.txtNhacc_TextChanged);
			// 
			// ckNguon
			// 
			this.ckNguon.BackColor = System.Drawing.SystemColors.HighlightText;
			this.ckNguon.CheckOnClick = true;
			this.ckNguon.Location = new System.Drawing.Point(347, 5);
			this.ckNguon.Name = "ckNguon";
			this.ckNguon.Size = new System.Drawing.Size(200, 274);
			this.ckNguon.TabIndex = 6;
			this.toolTip1.SetToolTip(this.ckNguon, "Các nguồn");
			this.ckNguon.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			// 
			// ckLoai
			// 
			this.ckLoai.BackColor = System.Drawing.SystemColors.HighlightText;
			this.ckLoai.CheckOnClick = true;
			this.ckLoai.Location = new System.Drawing.Point(58, 98);
			this.ckLoai.Name = "ckLoai";
			this.ckLoai.Size = new System.Drawing.Size(286, 184);
			this.ckLoai.TabIndex = 7;
			this.toolTip1.SetToolTip(this.ckLoai, "Nhóm kế toán");
			this.ckLoai.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			// 
			// butKetthuc
			// 
			this.butKetthuc.Image = ((System.Drawing.Bitmap)(resources.GetObject("butKetthuc.Image")));
			this.butKetthuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
			this.butKetthuc.Location = new System.Drawing.Point(277, 312);
			this.butKetthuc.Name = "butKetthuc";
			this.butKetthuc.Size = new System.Drawing.Size(75, 28);
			this.butKetthuc.TabIndex = 10;
			this.butKetthuc.Text = "    &Kết thúc";
			this.butKetthuc.Click += new System.EventHandler(this.butKetthuc_Click);
			// 
			// butIn
			// 
			this.butIn.Image = ((System.Drawing.Bitmap)(resources.GetObject("butIn.Image")));
			this.butIn.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
			this.butIn.Location = new System.Drawing.Point(200, 312);
			this.butIn.Name = "butIn";
			this.butIn.Size = new System.Drawing.Size(75, 28);
			this.butIn.TabIndex = 9;
			this.butIn.Text = "   &Excel";
			this.butIn.Click += new System.EventHandler(this.butIn_Click);
			// 
			// lstNhacc
			// 
			this.lstNhacc.ColumnCount = 0;
			this.lstNhacc.Location = new System.Drawing.Point(16, 162);
			this.lstNhacc.MatchBufferTimeOut = 1000;
			this.lstNhacc.MatchEntryStyle = AsYetUnnamed.MatchEntryStyle.FirstLetterInsensitive;
			this.lstNhacc.Name = "lstNhacc";
			this.lstNhacc.Size = new System.Drawing.Size(32, 4);
			this.lstNhacc.TabIndex = 15;
			this.lstNhacc.TextIndex = -1;
			this.lstNhacc.TextMember = null;
			this.lstNhacc.ValueIndex = -1;
			this.lstNhacc.Visible = false;
			// 
			// chkChitiet
			// 
			this.chkChitiet.Location = new System.Drawing.Point(58, 282);
			this.chkChitiet.Name = "chkChitiet";
			this.chkChitiet.Size = new System.Drawing.Size(168, 24);
			this.chkChitiet.TabIndex = 8;
			this.chkChitiet.Text = "Chi tiết theo hóa đơn";
			this.chkChitiet.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			// 
			// txtDN
			// 
			this.txtDN.CustomFormat = "dd/MM/yyyy HH:mm";
			this.txtDN.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
			this.txtDN.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
			this.txtDN.Location = new System.Drawing.Point(231, 5);
			this.txtDN.Name = "txtDN";
			this.txtDN.Size = new System.Drawing.Size(115, 21);
			this.txtDN.TabIndex = 1;
			this.txtDN.Value = new System.DateTime(2005, 5, 26, 11, 8, 30, 30);
			this.txtDN.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			// 
			// label4
			// 
			this.label4.Location = new System.Drawing.Point(172, 5);
			this.label4.Name = "label4";
			this.label4.Size = new System.Drawing.Size(59, 23);
			this.label4.TabIndex = 14;
			this.label4.Text = "đến ngày :";
			this.label4.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
			// 
			// lblNhomNguon
			// 
			this.lblNhomNguon.Image = ((System.Drawing.Bitmap)(resources.GetObject("lblNhomNguon.Image")));
			this.lblNhomNguon.Location = new System.Drawing.Point(491, 288);
			this.lblNhomNguon.Name = "lblNhomNguon";
			this.lblNhomNguon.Size = new System.Drawing.Size(20, 23);
			this.lblNhomNguon.TabIndex = 18;
			this.lblNhomNguon.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
			this.toolTip1.SetToolTip(this.lblNhomNguon, "Khai báo nhóm kế toán");
			this.lblNhomNguon.Click += new System.EventHandler(this.lblNhomNguon_Click);
			// 
			// chkNhomnguon
			// 
			this.chkNhomnguon.ImageAlign = System.Drawing.ContentAlignment.MiddleRight;
			this.chkNhomnguon.Location = new System.Drawing.Point(347, 288);
			this.chkNhomnguon.Name = "chkNhomnguon";
			this.chkNhomnguon.Size = new System.Drawing.Size(157, 24);
			this.chkNhomnguon.TabIndex = 16;
			this.chkNhomnguon.Text = "Báo cáo theo nhóm nguồn";
			this.chkNhomnguon.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			this.chkNhomnguon.CheckedChanged += new System.EventHandler(this.chkNhomnguon_CheckedChanged);
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(0, 98);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(56, 23);
			this.label2.TabIndex = 17;
			this.label2.Text = "Kế toán :";
			this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
			// 
			// chkNgayud
			// 
			this.chkNgayud.Location = new System.Drawing.Point(256, 30);
			this.chkNgayud.Name = "chkNgayud";
			this.chkNgayud.Size = new System.Drawing.Size(96, 24);
			this.chkNgayud.TabIndex = 19;
			this.chkNgayud.Text = "Ngày cập nhật";
			this.chkNgayud.KeyDown += new System.Windows.Forms.KeyEventHandler(this.tu_KeyDown);
			this.chkNgayud.CheckedChanged += new System.EventHandler(this.chkNgayud_CheckedChanged);
			// 
			// label3
			// 
			this.label3.Location = new System.Drawing.Point(0, 30);
			this.label3.Name = "label3";
			this.label3.Size = new System.Drawing.Size(56, 23);
			this.label3.TabIndex = 20;
			this.label3.Text = "Ngày BC :";
			this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
			// 
			// cbNhomkho
			// 
			this.cbNhomkho.BackColor = System.Drawing.SystemColors.HighlightText;
			this.cbNhomkho.Location = new System.Drawing.Point(58, 54);
			this.cbNhomkho.Name = "cbNhomkho";
			this.cbNhomkho.Size = new System.Drawing.Size(286, 21);
			this.cbNhomkho.TabIndex = 21;
			this.cbNhomkho.SelectedIndexChanged += new System.EventHandler(this.cbNhomkho_SelectedIndexChanged);
			// 
			// label6
			// 
			this.label6.Location = new System.Drawing.Point(-5, 54);
			this.label6.Name = "label6";
			this.label6.Size = new System.Drawing.Size(62, 23);
			this.label6.TabIndex = 22;
			this.label6.Text = "Nhóm kho :";
			this.label6.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
			// 
			// frmCongno_Nguon
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(552, 357);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.label6,
																		  this.cbNhomkho,
																		  this.label3,
																		  this.lblNhomNguon,
																		  this.txtDN,
																		  this.label4,
																		  this.lstNhacc,
																		  this.butKetthuc,
																		  this.butIn,
																		  this.ckLoai,
																		  this.ckNguon,
																		  this.txtNhacc,
																		  this.txtMa,
																		  this.label5,
																		  this.chkNgaysp,
																		  this.chkNgayhd,
																		  this.txtTN,
																		  this.label1,
																		  this.chkNhomnguon,
																		  this.chkChitiet,
																		  this.chkNgayud,
																		  this.label2});
			this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
			this.Name = "frmCongno_Nguon";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
			this.Text = "Báo cáo công nợ quá hạn";
			this.Load += new System.EventHandler(this.frmCongno_Nguon_Load);
			this.ResumeLayout(false);

		}
		#endregion

		private void frmCongno_Nguon_Load(object sender, System.EventArgs e)
		{
			f_Load_Prepare();
			f_Load_Nhomnguon();
			txtTN.Focus();
		}

		private void f_Load_Prepare()
		{
			txtTN.Value=DateTime.Now.AddYears(-1);
			txtDN.Value=DateTime.Now;
			
			cbNhomkho.DisplayMember="TEN";
			cbNhomkho.ValueMember="ID";
			cbNhomkho.DataSource=d.get_data("select id,ten from "+m_userroot+".d_dmnhomkho where id>0 order by loai,stt asc").Tables[0];
			cbNhomkho.SelectedValue=i_nhomkho.ToString();
		}

		private void f_Load_DM()
		{
			dsNhacungcap=d.get_data("select ma,ten,id,ma from "+m_userroot+".d_dmnx where nhom="+i_nhomkho.ToString()+" order by stt");
			lstNhacc.DisplayMember="TEN";
			lstNhacc.ValueMember="TEN";
			lstNhacc.DataSource=dsNhacungcap.Tables[0];
			
			ckNguon.Items.Clear();
			ckLoai.Items.Clear();
			dtNhomketoan=d.get_data("select * from "+m_userroot+".d_dmnhomkt where nhom="+i_nhomkho.ToString()+" order by stt").Tables[0];
			dtNguon=d.get_data("select a.id,a.ten,'cot_'||a.id as cot from "+m_userroot+".d_dmnguon a where nhom="+i_nhomkho.ToString()+" order by stt").Tables[0];
			if(dtNguon.Rows.Count==0 || !d.bQuanlynguon(i_nhomkho)) dtNguon.Rows.Add(new string[] {"0","Số tiền","cot_0"});
			foreach(DataRow _r in dtNguon.Rows) ckNguon.Items.Add(_r["ten"].ToString());
			foreach(DataRow _r in dtNhomketoan.Rows) ckLoai.Items.Add(_r["ten"].ToString());
			lblNhomNguon.Visible=chkNhomnguon.Visible=ckNguon.Enabled=d.bQuanlynguon(i_nhomkho);
			if(dtNhomketoan.Rows.Count==0)
			{
				MessageBox.Show("Bạn phải khai báo ít nhất 1 nhóm kế toán !",d.Msg);
				butIn.Enabled=false;
				butKetthuc.Focus();
				return;
			}
		}

		private void f_Load_Nhomnguon()
		{
			if(d.get_data("select * from dba_tables where lower(owner)='"+m_userroot+"' and lower(table_name)='bc_nhomnguon'").Tables[0].Rows.Count==0)
			{
				d.execute_data("create table "+m_userroot+".bc_nhomnguon(id number(2),stt number(2) default 0,ten nvarchar2(254),idnguon number(2) default 0,nhom number(2) default 0,constraint pk_bc_nhomnguon primary key (id,idnguon))");
			}
			string _asql="";
			dtNhomNguon=new System.Data.DataTable();
			dtNhomNguon.Columns.Add("id",typeof(int));
			dtNhomNguon.Columns.Add("stt",typeof(int));
			dtNhomNguon.Columns.Add("idnguon",typeof(string));
			dtNhomNguon.Columns.Add("ten",typeof(string));
			
			foreach(DataRow r in d.get_data("select a.id,a.idnguon,a.ten from "+m_userroot+".bc_nhomnguon a where nhom="+i_nhomkho.ToString()+" order by stt").Tables[0].Rows)
			{
				_asql="id="+r["id"].ToString();
				DataRow _r=d.getrowbyid(dtNhomNguon,_asql);
				if(_r==null)
				{
					DataRow r1=dtNhomNguon.NewRow();
					r1["id"]=r["id"].ToString();
					r1["idnguon"]=","+r["idnguon"].ToString()+",";
					r1["ten"]=r["ten"].ToString();
					dtNhomNguon.Rows.Add(r1);
				}
				else
				{
					_r["idnguon"]=_r["idnguon"].ToString()+r["idnguon"].ToString()+",";
				}
			}
			foreach(DataRow r in d.get_data("select * from "+m_userroot+".d_dmnguon b where b.nhom="+i_nhomkho.ToString()+" and b.id not in(select a.idnguon from "+m_userroot+".bc_nhomnguon a where a.nhom="+i_nhomkho.ToString()+") order by b.stt").Tables[0].Rows)
			{
				_asql="id=99";
				DataRow _r=d.getrowbyid(dtNhomNguon,_asql);
				if(_r==null)
				{
					DataRow r1=dtNhomNguon.NewRow();
					r1["id"]="99";
					r1["idnguon"]=","+r["id"].ToString()+",";
					r1["ten"]="Khác";
					dtNhomNguon.Rows.Add(r1);
				}
				else
				{
					_r["idnguon"]=_r["idnguon"].ToString()+r["id"].ToString()+",";
				}
			}
		}

		private void f_Taodataset()
		{
			dsCongno=new DataSet();
			dsCongno.Tables.Add("Table");
			dsCongno.Tables[0].Columns.Add("stt",typeof(decimal));
			dsCongno.Tables[0].Columns.Add("manhacc",typeof(decimal));
			dsCongno.Tables[0].Columns.Add("nhacc",typeof(string));
			dsCongno.Tables[0].Columns.Add("manhomkt",typeof(string));
			dsCongno.Tables[0].Columns.Add("nhomkt",typeof(string));
	
			foreach(string _nguon in _admnguon.Split('@'))
			{
				foreach(DataRow r in (!chkNhomnguon.Checked) ? dtNguon.Rows : dtNhomNguon.Rows) dsCongno.Tables[0].Columns.Add("cot_"+_nguon+"_"+r["id"].ToString().PadLeft(2,'0'),typeof(decimal)).DefaultValue=0;
			}
			dsCongno.Tables[0].Columns.Add("tongcong",typeof(decimal)).DefaultValue=0;
			dsCongno.Tables[0].Columns.Add("ghichu",typeof(string));
			dsCongno.AcceptChanges();
		}

		private void butKetthuc_Click(object sender, System.EventArgs e)
		{
			System.GC.Collect();
			this.Close();
		}

		private void chkNgayhd_CheckedChanged(object sender, System.EventArgs e)
		{
			if(this.ActiveControl==chkNgayhd)
			{
				if(chkNgaysp.Checked ||chkNgayud.Checked) 
				{
					chkNgaysp.Checked=false;
					chkNgayud.Checked=false;
				}
			}
		}

		private void chkNgaysp_CheckedChanged(object sender, System.EventArgs e)
		{
			if(this.ActiveControl==chkNgaysp)
			{
				if(chkNgayhd.Checked ||chkNgayud.Checked) 
				{
					chkNgayhd.Checked=false;
					chkNgayud.Checked=false;
				}
			}
		}

		private void txtNhacc_TextChanged(object sender, System.EventArgs e)
		{
			lstNhacc.BrowseToText(txtNhacc,txtMa,ckNguon,txtMa.Location.X,txtMa.Location.Y+20,txtMa.Width+txtNhacc.Width,80);
		}

		private void f_Getdieukien()
		{
			_angaybaocao="";
			_amaketoan="";
			_amanguon="";
			_amanhacc="";
			_angaybaocao=(chkNgayud.Checked) ? txtDN.Text.Substring(0,16):txtDN.Text.Substring(0,10);
			_amanguon=f_GetMaControl(ckNguon,dtNguon,false);
			if(chkNhomnguon.Checked) _amanguon="";
			_amaketoan=f_GetMaControl(ckLoai,dtNhomketoan,false);
			if(txtMa.Text!="")
			{
				try
				{
					_amanhacc=dsNhacungcap.Tables[0].Select("ma='"+txtMa.Text+"'")[0]["id"].ToString();
				}
				catch{_amanhacc="";}
			}
		}

		private string f_GetMaControl(CheckedListBox lst,System.Data.DataTable dt,bool bAll)
		{
			string _tmp="";
			for(int i=0;i<lst.Items.Count;i++)
			{
				if(lst.GetItemChecked(i)) _tmp +=dt.Rows[i]["id"].ToString()+",";
			}
			if(_tmp=="" && bAll) 
			{
				for(int i=0;i<lst.Items.Count;i++)
				{
					_tmp+=dt.Rows[i]["id"].ToString()+",";
					lst.SetItemChecked(i,true);
				}
			}
			_tmp=_tmp.TrimEnd(',');
			return _tmp;
		}

		private void f_GetData()
		{
			dsCongno.Tables[0].Clear();
			DateTime dt1=d.StringToDate(txtTN.Text).AddDays(-d.iNgaykiemke);
			DateTime dt2=d.StringToDate(txtDN.Text).AddDays(d.iNgaykiemke);
			int y1=dt1.Year,m1=dt1.Month;
			int y2=dt2.Year,m2=dt2.Month;
			int itu,iden;
			string _mmyy="";
			string d_user="",sql="";
			bool be=false;
			DataSet _ads=new DataSet();

			for(int i=y1;i<=y2;i++)
			{
				itu=(i==y1)?m1:1;
				iden=(i==y2)?m2:12;
				for(int j=itu;j<=iden;j++)
				{
					_mmyy=j.ToString().PadLeft(2,'0')+i.ToString().Substring(2,2);
					if (d.bMmyy(_mmyy))
					{
						d_user=m_userroot+"d"+_mmyy;
						
						sql="select c.id as manhacc, c.ten nhacc,nvl(f.id,9999) as manhomkt, nvl(f.ten,'') as nhomkt, nvl(g.id,999) manguon, sum(a1.sotien) as sotien,";
						if(chkChitiet.Checked) sql+=" a.sohd,";
						if(!chkNgayhd.Checked && !chkNgaysp.Checked && !chkNgayud.Checked)
						{
							sql+="to_date('"+_angaybaocao+"','dd/mm/yy')-to_date(a.ngayhd,'dd/mm/yy') as songayqh";
						}
						else if(chkNgayhd.Checked)
						{
							sql+="to_date('"+_angaybaocao+"','dd/mm/yy')-to_date(a.ngayhd,'dd/mm/yy') as songayqh";
						}
						else if(chkNgaysp.Checked)
						{
							sql+="to_date('"+_angaybaocao+"','dd/mm/yy')-to_date(a.ngaysp,'dd/mm/yy') as songayqh";
						}
						else if(chkNgayud.Checked)
						{
							sql+="round(to_date('"+_angaybaocao+"','dd/mm/yyyy hh24:mi')-to_date(to_char(a.ngayud,'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi'),0) as songayqh";
						}
						sql+=" from "+d_user+".d_nhapll a, "+d_user+".d_nhapct a1, "+d_user+".d_thanhtoan b, "+m_userroot+".d_dmnx c, "+m_userroot+".d_dmbd d, "+m_userroot+".d_dmnhomkt f, "+m_userroot+".d_dmnguon g";
						sql+=" where a.id=b.id and a.madv=c.id and a.loai='M' and b.sotien<>0 and a.id=a1.id and a.nhom="+i_nhomkho;
						sql+=" and a1.mabd=d.id and d.sotk=f.id(+) and a.manguon=g.id(+)";
						sql+=" and nvl(b.sotien,0)-nvl(b.datra,0)>0";
						if(_amanhacc!="") sql+=" and a.madv in("+_amanhacc+")";
						if(_amanguon!="") sql+=" and a.manguon in("+_amanguon+")";
						if(_amaketoan!="") sql+=" and d.sotk in("+_amaketoan+")";
						if(!chkNgayhd.Checked && !chkNgaysp.Checked && !chkNgayud.Checked)
						{
							sql+=" and to_date(a.ngayhd,'dd/mm/yy') between to_date('"+txtTN.Text.Substring(0,10)+"','dd/mm/yy') and to_date('"+txtDN.Text.Substring(0,10)+"','dd/mm/yy')";
						}
						else if(chkNgayhd.Checked)
						{
							sql+=" and to_date(a.ngayhd,'dd/mm/yy') between to_date('"+txtTN.Text.Substring(0,10)+"','dd/mm/yy') and to_date('"+txtDN.Text.Substring(0,10)+"','dd/mm/yy')";
						}
						else if(chkNgaysp.Checked)
						{
							sql+=" and to_date(a.ngaysp,'dd/mm/yy') between to_date('"+txtTN.Text.Substring(0,10)+"','dd/mm/yy') and to_date('"+txtDN.Text.Substring(0,10)+"','dd/mm/yy')";
						}
						else if(chkNgayud.Checked)
						{
							sql+=" and to_date(to_char(a.ngayud,'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') between to_date('"+txtTN.Text.Substring(0,16)+"','dd/mm/yyyy hh24:mi') and to_date('"+txtDN.Text.Substring(0,16)+"','dd/mm/yyyy hh24:mi')";
						}
						sql+=" group by c.id, c.ten , nvl(f.id,9999), nvl(f.ten,''), nvl(g.id,999)";//, to_date(a.ngaysp,'dd/mm/yy')";
						if(chkChitiet.Checked) sql+=" ,a.sohd";
						if(!chkNgayhd.Checked && !chkNgaysp.Checked && !chkNgayud.Checked)
						{
							sql+=",to_date(a.ngayhd,'dd/mm/yy'), to_date('"+_angaybaocao+"','dd/mm/yy')";
						}
						else if(chkNgayhd.Checked)
						{
							sql+=",to_date(a.ngayhd,'dd/mm/yy'), to_date('"+_angaybaocao+"','dd/mm/yy')";
						}
						else if(chkNgaysp.Checked)
						{
							sql+=",to_date(a.ngaysp,'dd/mm/yy'), to_date('"+_angaybaocao+"','dd/mm/yy')";
						}
						else if(chkNgayud.Checked)
						{
							sql+=",to_date(to_char(a.ngayud,'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi'), to_date('"+_angaybaocao+"','dd/mm/yyyy hh24:mi')";
						}
						if(!be)
						{
							_ads=d.get_data(sql);
							be=true;
						}
						else _ads.Merge(d.get_data(sql));				
					}
				}
			}
			
			//foreach(DataRow r in d.get_data("select * from "+m_userroot+".d_table where substr(mmyy,3)<='"+_mmyy.Substring(2,2)+"' and substr(mmyy,0,2)<='"+_mmyy.Substring(0,2)+"'").Tables[0].Rows)
			
			string _asql="";int _ii=0;int i_stt=0;
			foreach(DataRow r in _ads.Tables[0].Select("","manhacc"))
			{
				_asql="manhacc="+r["manhacc"].ToString()+"";
				DataRow r1=d.getrowbyid(dsCongno.Tables[0],_asql);
				if(r1==null)
				{
					_ii=0;
					foreach(DataRow r2 in dtNhomketoan.Rows)
					{
						DataRow r4;
						if(_ii==0) 
						{
							r4=dsCongno.Tables[0].NewRow();
							i_stt++;
							r4["stt"]=i_stt;
							_ii++;
							r4["nhacc"]=r["nhacc"].ToString();
							r4["manhacc"]=decimal.Parse(r["manhacc"].ToString());
							r4["manhomkt"]="0";
							r4["nhomkt"]="Tổng cộng";
							dsCongno.Tables[0].Rows.Add(r4);
						}
						r4=dsCongno.Tables[0].NewRow();
						r4["manhacc"]=decimal.Parse(r["manhacc"].ToString());
						r4["manhomkt"]=r2["id"].ToString();
						r4["nhomkt"]=r2["ten"].ToString();
						if(chkChitiet.Checked) r4["nhomkt"]=r2["ten"].ToString()+" - Số hóa đơn : ";
						dsCongno.Tables[0].Rows.Add(r4);
					}
					dsCongno.Tables[0].AcceptChanges();
				}
			}
			
			string atitle="";_amanguon="";
			foreach(DataRow r in _ads.Tables[0].Select("","manhacc"))
			{
				_amanguon=(!chkNhomnguon.Checked)?r["manguon"].ToString():dtNhomNguon.Select("idnguon like '%,"+r["manguon"].ToString()+",%'")[0]["id"].ToString();
				_asql="manhacc="+r["manhacc"].ToString()+" and (manhomkt='"+r["manhomkt"].ToString()+"' or manhomkt='9999')";
				DataRow r1=d.getrowbyid(dsCongno.Tables[0],_asql);
				if(long.Parse(r["songayqh"].ToString())<=30) atitle="cot_30_"+_amanguon.PadLeft(2,'0');
				else if(long.Parse(r["songayqh"].ToString())<=45) atitle="cot_45_"+_amanguon.PadLeft(2,'0');
				else if(long.Parse(r["songayqh"].ToString())<=60) atitle="cot_60_"+_amanguon.PadLeft(2,'0');
				else if(long.Parse(r["songayqh"].ToString())<=75) atitle="cot_75_"+_amanguon.PadLeft(2,'0');
				else if(long.Parse(r["songayqh"].ToString())<=90) atitle="cot_90_"+_amanguon.PadLeft(2,'0');
				else atitle="cot_91_"+_amanguon.PadLeft(2,'0');
				if(r1==null)
				{
					DataRow r4=dsCongno.Tables[0].NewRow();
					r4["manhacc"]=decimal.Parse(r["manhacc"].ToString());
					r4["manhomkt"]="9999";
					r4["nhomkt"]="Khác";
					if(chkChitiet.Checked) r4["nhomkt"]="Khác - Số hóa đơn :"+r["sohd"].ToString()+";";
					r4[atitle]=decimal.Parse(r["sotien"].ToString());
					r4["tongcong"]=decimal.Parse(r["sotien"].ToString());
					dsCongno.Tables[0].Rows.Add(r4);
				}
				else
				{
					r1[atitle]=decimal.Parse(r1[atitle].ToString())+decimal.Parse(r["sotien"].ToString());
					if(chkChitiet.Checked) r1["nhomkt"]=r1["nhomkt"].ToString()+r["sohd"].ToString()+";";
					r1["tongcong"]=decimal.Parse(r1["tongcong"].ToString())+decimal.Parse(r["sotien"].ToString());
				}
				r1=d.getrowbyid(dsCongno.Tables[0],"manhacc="+r["manhacc"].ToString()+" and manhomkt='0'");
				if(r1!=null)
				{
					r1[atitle]=decimal.Parse(r1[atitle].ToString())+decimal.Parse(r["sotien"].ToString());
					r1["tongcong"]=decimal.Parse(r1["tongcong"].ToString())+decimal.Parse(r["sotien"].ToString());
				}
			}
			
			if(dsCongno.Tables[0].Rows.Count<=0)
			{
				MessageBox.Show("Không có số liệu !",d.Msg);
				butKetthuc.Focus();
				return;
			}
			if(_amaketoan!="")
			{
				string _makt=_amaketoan.TrimEnd(',')+","+"0,9999";
				foreach(DataRow r in dsCongno.Tables[0].Select("manhomkt not in("+_makt+")"))
					dsCongno.Tables[0].Rows.Remove(r);
				dsCongno.AcceptChanges();
			}
		}

		private void butIn_Click(object sender, System.EventArgs e)
		{
			this.Cursor=Cursors.WaitCursor;
			if(!chkNgayhd.Checked && !chkNgaysp.Checked && !chkNgayud.Checked)
			{
				MessageBox.Show("Chọn kiểu ngày báo cáo !",d.Msg);
				chkNgayhd.Focus();
				this.Cursor=Cursors.Default;
				return;
			}

			f_Taodataset();

			f_Getdieukien();

			f_GetData();

			this.Cursor=Cursors.Default;
			
			dsxml=new DataSet();
			dsxml=(!chkNhomnguon.Checked)? f_XoaCot("stt@nhacc@manhacc@nhomkt@manhomkt@ghichu"):dsCongno.Copy();
			if(chkChitiet.Checked)
			{
				string _st="";
				foreach(DataRow r in dsxml.Tables[0].Rows)
				{
					_st=r["nhomkt"].ToString().TrimEnd();
					_st=(_st.Substring(_st.Length-1,1)==";")?_st.Replace("- Số hóa đơn :","").TrimEnd():_st;
					r["nhomkt"]=_st;
				}
			}
			DataSet ds1=dsxml.Clone();
			foreach(DataRow r in dsxml.Tables[0].Rows) if(r["tongcong"].ToString()!="0" && r["tongcong"].ToString()!="") ds1.Tables[0].Rows.Add(r.ItemArray);
			dsxml=ds1.Copy();
			dsxml.AcceptChanges();
						
			excel(false);

			butKetthuc.Focus();
		}

		private string f_Gettencot(string _st)
		{
			string _tmp="";
			foreach(string _s in _admnguon.Split('@'))
			{
				foreach(DataRow r in dtNguon.Rows)
				{
					if(_st=="cot_"+_s+"_"+r["id"].ToString().PadLeft(2,'0')) 
					{
						_tmp=r["ten"].ToString();
						break;
					}
				}
			}
			return _tmp;
		}

		private void excel(bool print)
		{
			d.check_process_Excel();
			try
			{
				DataSet _ads=new DataSet();
				_ads.Merge(dsxml.Tables[0].Select("","manhacc"));
				_ads.Tables[0].Columns.Remove("manhacc");
				_ads.Tables[0].Columns.Remove("manhomkt");
				_ads.AcceptChanges();
				string atitle="STT@Tên Cty@Loại";
				string[] _ratitle=atitle.Split('@');
				int be=8,sodong=_ads.Tables[0].Rows.Count+be,socot=_ads.Tables[0].Columns.Count-4,dongke=sodong;
				string tenfile = d.Export_Excel(_ads.Tables[0], "Qlycongno");
				oxl=new Excel.Application();
				owb=(Excel._Workbook)(oxl.Workbooks.Open(tenfile,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value));//,Missing.Value,Missing.Value));
				osheet=(Excel._Worksheet)owb.ActiveSheet;
				oxl.ActiveWindow.DisplayGridlines=true;
				for(int i=0;i<be;i++) osheet.get_Range(d.getIndex(i)+"1",d.getIndex(i)+"1").EntireRow.Insert(Missing.Value);//,Missing.Value);
				osheet.get_Range(d.getIndex(0)+Convert.ToString(be),d.getIndex(socot+3)+Convert.ToString(dongke+2)).Borders.LineStyle=XlBorderWeight.xlHairline;
				for(int i=0;i<_ratitle.Length;i++) osheet.Cells[be-1+2,i+1]=_ratitle[i].ToString();
				int _icotnguon=_ratitle.Length+1;
				int _icongayqh=_ratitle.Length+1;
				if(!chkNhomnguon.Checked)
				{
					string _tmp="";
					for(int i=_icotnguon-1;i<_ads.Tables[0].Columns.Count-1;i++)
					{
						_tmp=f_Gettencot(_ads.Tables[0].Columns[i].ColumnName);
						osheet.Cells[be-1+2,i+1]=_tmp;
					}
					_icotnguon++;

					_tmp="";
					int _i1=0,_i2=0;
					_i1=_ratitle.Length;
					_i2=_ratitle.Length;
					for(int i=_ratitle.Length;i<_ads.Tables[0].Columns.Count-1;i++)
					{
						_tmp=_ads.Tables[0].Columns[i].ColumnName.Substring(0,6);
						string _str=_tmp.Substring(4,2);
						try
						{
							if(_tmp!=_ads.Tables[0].Columns[i+1].ColumnName.Substring(0,6) && _ads.Tables[0].Columns[i].ColumnName!="tongcong")
							{
								_i2=i;
								orange=osheet.get_Range(d.getIndex(_i1)+Convert.ToString(be),d.getIndex(_i2)+Convert.ToString(be));
								orange.Merge(false);
								orange.Font.Bold=true;
								orange.Font.Size=12;								
								orange.Font.ColorIndex = 41;
								orange.Font.FontStyle="Bold Italic";
								orange.HorizontalAlignment=XlHAlign.xlHAlignCenter;
								orange.Value=(_str=="30")  ? "Dưới 30 ngày" : (_str=="45" ? "Từ 31 đến 45 ngày" : (_str=="60")?"Từ 46 đến 60 ngày": (_str=="75")? "Từ 61 đến 75 ngày" :(_str=="90")?"Từ 76 đến 90 ngày":"Trên 90 ngày");
								_i1=i+1;
							}
						}
						catch{}
					}
				}
				else
				{
					try
					{
						foreach(string _str in _admnguon.Split('@'))
						{
							_icongayqh=(_icongayqh==_icotnguon) ? _icongayqh : _icotnguon;
							foreach(DataRow r in dtNhomNguon.Rows) 
							{
								osheet.Cells[be-1+2,_icotnguon]=r["ten"].ToString();
								_icotnguon++;
							}
						
							orange=osheet.get_Range(d.getIndex(_icongayqh-1)+Convert.ToString(be),d.getIndex(_icotnguon-2)+Convert.ToString(be));
							orange.Merge(false);
							orange.Font.Bold=true;
							orange.Font.Size=12;
							//						orange.Interior.ColorIndex=34;
							orange.Font.ColorIndex = 41;
							orange.Font.FontStyle="Bold Italic";
							orange.HorizontalAlignment=XlHAlign.xlHAlignCenter;
							orange.Value=(_str=="30")  ? "Dưới 30 ngày" : (_str=="45" ? "Từ 31 đến 45 ngày" : (_str=="60")?"Từ 46 đến 60 ngày": (_str=="75")? "Từ 61 đến 75 ngày" :(_str=="90")?"Từ 76 đến 90 ngày":"Trên 90 ngày");
						}
					}
					catch{}
				}
				try
				{
					_icotnguon=_ads.Tables[0].Columns.Count-1;
					_icongayqh=_icotnguon;
					orange=osheet.get_Range(d.getIndex(_icongayqh-1)+Convert.ToString(be),d.getIndex(_icotnguon-1)+Convert.ToString(be+1));
					orange.Merge(false);
					orange.Value="Tổng cộng";
					orange.HorizontalAlignment=XlHAlign.xlHAlignCenter;
					orange.VerticalAlignment=XlVAlign.xlVAlignCenter;
					orange.Font.Bold=true;

					_icotnguon++;_icongayqh=_icotnguon;
					orange=osheet.get_Range(d.getIndex(_icongayqh-1)+Convert.ToString(be),d.getIndex(_icotnguon-1)+Convert.ToString(be+1));
					orange.Merge(false);
					orange.Value="Ghi chú";

					orange=osheet.get_Range(d.getIndex(0)+Convert.ToString(be),d.getIndex(0)+Convert.ToString(be+1));
					orange.Merge(false);
					orange=osheet.get_Range(d.getIndex(1)+Convert.ToString(be),d.getIndex(1)+Convert.ToString(be+1));
					orange.Merge(false);
					orange=osheet.get_Range(d.getIndex(2)+Convert.ToString(be),d.getIndex(2)+Convert.ToString(be+1));
					orange.Merge(false);
				}
				catch{}
				for (int k = _ratitle.Length+1; k <= _ads.Tables[0].Columns.Count-1; k++)
				{
					osheet.Cells[sodong+2, k] = "=SUM(" + d.getIndex(k - 1) + ""+Convert.ToString(be+2)+":" + d.getIndex(k - 1)+(sodong+1).ToString()+")/2";
				}
				for (int k=0;k<_ads.Tables[0].Rows.Count;k++)
				{
					try
					{
						if(_ads.Tables[0].Rows[k]["nhomkt"].ToString()=="Tổng cộng")
						{
							orange=osheet.get_Range(d.getIndex(0)+Convert.ToString(be+k+2),d.getIndex(_ads.Tables[0].Columns.Count-1)+Convert.ToString(be+k+2));
							orange.Font.ColorIndex = 41;
							orange.Font.FontStyle="Bold Italic";
						}
					}
					catch{}
				}
				orange=osheet.get_Range(d.getIndex(_ratitle.Length)+Convert.ToString(be+2),d.getIndex(_ads.Tables[0].Columns.Count-1)+Convert.ToString(sodong+2));
				orange.Select();
				orange.NumberFormat="#,##0.00";

				orange=osheet.get_Range(d.getIndex(0)+Convert.ToString(sodong+2),d.getIndex(2)+Convert.ToString(sodong+2));
				orange.Merge(false);
				orange.Value="Tổng cộng:";
				orange.HorizontalAlignment=XlHAlign.xlHAlignCenter;

				orange=osheet.get_Range(d.getIndex(0)+Convert.ToString(sodong+2),d.getIndex(_ads.Tables[0].Columns.Count-1)+Convert.ToString(sodong+2));
				orange.Font.FontStyle="Bold Italic";
				orange.Interior.ColorIndex=34;
								
				orange=osheet.get_Range(d.getIndex(0)+"1",d.getIndex(socot+3)+Convert.ToString(sodong+2));
				orange.Font.Name="Arial";
				orange.Font.Size=8;
				orange.EntireColumn.AutoFit();

				oxl.ActiveWindow.DisplayZeros=false;
				osheet.PageSetup.Orientation=XlPageOrientation.xlLandscape;
				osheet.PageSetup.PaperSize=XlPaperSize.xlPaperA4;
				osheet.PageSetup.LeftMargin=20;
				osheet.PageSetup.RightMargin=20;
				osheet.PageSetup.TopMargin=30;
				osheet.PageSetup.CenterFooter="Trang : &P/&N";
				osheet.Cells[1,1]=d.Syte;osheet.Cells[2,1]=d.Tenbv;
				orange=osheet.get_Range(d.getIndex(1)+"1",d.getIndex(3)+"2");
				orange.HorizontalAlignment=XlHAlign.xlHAlignCenterAcrossSelection;
				
				string angaybc ="Ngày "+txtDN.Value.Day.ToString().PadLeft(2,'0')+" tháng "+txtDN.Value.Month.ToString().PadLeft(2,'0')+" năm "+txtDN.Value.Year.ToString();
				string angayin ="Ngày "+DateTime.Now.Day.ToString().PadLeft(2,'0')+" tháng "+DateTime.Now.Month.ToString().PadLeft(2,'0')+" năm "+DateTime.Now.Year.ToString();
				orange=osheet.get_Range(d.getIndex(1)+"4",d.getIndex(_ads.Tables[0].Columns.Count-2)+"4");
				orange.Merge(false);
				orange.Value="BÁO CÁO CÔNG NỢ ĐẾN : "+angaybc;
				orange.Font.Bold=true;
				orange.Font.Size=14;
				orange.HorizontalAlignment=XlHAlign.xlHAlignCenter;

				//osheet.Cells[sodong+4,socot-1]= "Quy Nhơn, "+angayin;
				orange=osheet.get_Range(d.getIndex(_ads.Tables[0].Columns.Count-4)+Convert.ToString(sodong+4),d.getIndex(_ads.Tables[0].Columns.Count-1)+Convert.ToString(sodong+4));
				orange.Merge(false);
				orange.Font.Italic=true;
				orange.Value=angayin;
				orange.HorizontalAlignment=XlHAlign.xlHAlignCenter;
				//osheet.Cells[sodong+5,socot-1]= "Người lập biểu";
				orange=osheet.get_Range(d.getIndex(_ads.Tables[0].Columns.Count-4)+Convert.ToString(sodong+5),d.getIndex(_ads.Tables[0].Columns.Count-1)+Convert.ToString(sodong+5));
				orange.Merge(false);
				orange.Font.Italic=true;
				orange.Value="Người lập biểu";
				orange.HorizontalAlignment=XlHAlign.xlHAlignCenter;
				
				if (print) osheet.PrintOut(Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
				else oxl.Visible=true;
				quitExcel();
			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}

		private void quitExcel()
		{
			if (osheet != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(osheet);
			}
			if (owb != null)
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject(owb);
			}
			if (oxl != null)
			{                
				System.Runtime.InteropServices.Marshal.ReleaseComObject(oxl);
			}

			osheet = null;
			owb = null;
			oxl = null;

			GC.Collect();
		}

		private void txtMa_Validated(object sender, System.EventArgs e)
		{
			if(txtMa.Text!="")
			{
				try
				{
					txtNhacc.Text=dsNhacungcap.Tables[0].Select("ma='"+txtMa.Text.Trim()+"'")[0]["ten"].ToString();
				}
				catch {txtNhacc.Text="";}
			}
		}

		private void txtNhacc_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Up||e.KeyCode==Keys.Down) 
			{
				if(lstNhacc.Visible) lstNhacc.Focus();
			}
			if(e.KeyCode==Keys.Enter) SendKeys.Send("{Tab}");
		}

		private void tu_KeyDown(object sender, System.Windows.Forms.KeyEventArgs e)
		{
			if(e.KeyCode==Keys.Enter) SendKeys.Send("{Tab}");
		}

		private DataSet f_XoaCot(string _st)
		{
			DataSet _ads=dsCongno.Copy();
			foreach(string _s in _st.Split('@')) _ads.Tables[0].Columns.Remove(_s);
			_ads.AcceptChanges();
			
			DataSet _ds=new DataSet();
			_ds.Tables.Add("Table");
			_ds.Tables[0].Columns.Add("ten",typeof(string));
			string _str="";
			for(int i=0;i<_ads.Tables[0].Rows.Count;i++) 
			{
				_ds.Tables[0].Columns.Add("cot_"+Convert.ToString(i),typeof(decimal)).DefaultValue=0;
				_str+="cot_"+Convert.ToString(i)+"+";
			}
			for(int i=0;i<_ads.Tables[0].Columns.Count;i++)
			{
				DataRow r1=_ds.Tables[0].NewRow();
				r1["ten"]=_ads.Tables[0].Columns[i].ColumnName.ToString();
				for(int j=1;j<_ads.Tables[0].Rows.Count;j++) r1[j]=0;
				_ds.Tables[0].Rows.Add(r1);
			}
			
			for(int i=0;i<_ds.Tables[0].Rows.Count;i++)
			{
				for(int j=0;j<_ads.Tables[0].Rows.Count;j++)
				{
					if(_ds.Tables[0].Columns[j].ToString().ToLower()!="ten")
					{
						_ds.Tables[0].Rows[i]["cot_"+j.ToString()]=decimal.Parse(_ads.Tables[0].Rows[j][i].ToString());
					}
					
				}
			}
			
			_ds.AcceptChanges();
			_str=_str.TrimEnd('+');
			decimal _tongcong=0;
			DataSet ds1=_ds.Clone();
			foreach(DataRow r in _ds.Tables[0].Rows) 
			{
				_tongcong=0;
				foreach(string _cot in _str.Split('+')) _tongcong+=decimal.Parse(r[_cot].ToString());
				if(_tongcong!=0) ds1.Tables[0].Rows.Add(r.ItemArray);
			}
			ds1.AcceptChanges();
			_ds=ds1.Copy();
			_ds.AcceptChanges();
			_st+="@";
			foreach(DataRow r in _ds.Tables[0].Rows) _st+=r["ten"].ToString()+"@";
			_ads=new DataSet();
			_ads=dsCongno.Copy();
			
			foreach(DataColumn dc in dsCongno.Tables[0].Columns)
			{
				if(_st.IndexOf(dc.ColumnName+"@")==-1) _ads.Tables[0].Columns.Remove(dc.ColumnName);
			}
			_ads.AcceptChanges();
			
			return _ads;
		}

		private void lblNhomNguon_Click(object sender, System.EventArgs e)
		{
			frmDmNhomnguon f = new frmDmNhomnguon(d,i_nhomkho);
			f.ShowDialog();
			f_Load_Nhomnguon();
		}

		private void chkNhomnguon_CheckedChanged(object sender, System.EventArgs e)
		{
			if(d.bQuanlynguon(i_nhomkho))
			{
				if(chkNhomnguon.Checked) ckNguon.Enabled=false;
				else ckNguon.Enabled=true;
			}
		}

		private void chkNgayud_CheckedChanged(object sender, System.EventArgs e)
		{
			if(this.ActiveControl==chkNgayud)
			{
				if(chkNgaysp.Checked ||chkNgayhd.Checked) 
				{
					chkNgaysp.Checked=false;
					chkNgayhd.Checked=false;
				}
			}
		}

		private void cbNhomkho_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			if(cbNhomkho.SelectedIndex!=-1 )//&& this.ActiveControl==cbNhomkho)
			{
				i_nhomkho=int.Parse(cbNhomkho.SelectedValue.ToString());
				f_Load_Nhomnguon();
				f_Load_DM();
			}
		}
	}
}
